create or replace
PROCEDURE SP_CONTROL_DE_FRAUDE AS

  err_num NUMBER;
  err_msg VARCHAR2(255);
  
  CURSOR cventas_producto IS
      select
          venta.de_placa as placa, 
          prod_tipo_vehiculo.va_cantidad_limite_prod as lim_producto, 
          prod_tipo_vehiculo.va_limite_transacciones as lim_transac,
          sum(venta.va_cantidad) as sum_cantidad, 
          venta.clpb_producto as producto,
          count(*) as contador
      from clpm_venta venta
          left join clpb_vehiculo vehiculo on venta.de_placa= vehiculo.de_placa
          left join clpb_tipo_vehiculo tipo_vehiculo on vehiculo.co_tipo_vehiculo= tipo_vehiculo.id
          left join clpr_producto_tipo_vehiculo prod_tipo_vehiculo on 
            tipo_vehiculo.id = prod_tipo_vehiculo.co_tipo_vehiculo and venta.clpb_producto = prod_tipo_vehiculo.co_producto
      where venta.st_venta='A'
      	  and (vehiculo.st_excluido_control_fraude = 'N' or vehiculo.st_excluido_control_fraude is null)
          and to_char(venta.da_fecha_venta, 'dd/MM/yyyy') = to_char((sysdate-1),'dd/MM/yyyy') 
          and venta.co_codigo_tarjeta_persona is null
          group by  venta.de_placa, prod_tipo_vehiculo.va_cantidad_limite_prod, 
          prod_tipo_vehiculo.va_limite_transacciones, venta.clpb_producto;
    
    
    CURSOR cventas_tarjeta IS
      select venta.co_codigo_tarjeta_persona as code_tarjeta, 
          persona.id as coClientePersona,
          sum(venta.va_cantidad) as sum_cantidad, 
          venta.clpb_producto as producto,
          count(*) as contador
      from clpm_venta venta join clpb_cliente_persona persona on venta.CLPB_CLIENTE_PERSONA = persona.id
      where venta.st_venta='A'
          and venta.co_codigo_tarjeta_persona is not null
          and to_char(venta.da_fecha_venta, 'dd/MM/yyyy') = to_char((sysdate-1), 'dd/MM/yyyy')
      group by  venta.co_codigo_tarjeta_persona,  persona.id , venta.clpb_producto;
    
 cventa_producto_rec cventas_producto%ROWTYPE; 
 cventa_tarjeta_rec cventas_tarjeta%ROWTYPE; 
 
 limite_transacciones_default NUMBER := 0;
 cantidad_lim_prod_default NUMBER := 0;
 limite_trans_tarj_default NUMBER := 0;
 cantidad_lim_prod_tarj_default NUMBER := 0;
 
 cantidad_limite_producto NUMBER := 0;
 cantidad_total_acum_cliente NUMBER :=0;
 limite_transacciones_tarjeta NUMBER := 0;
 limite_transac_tarjeta_cliente NUMBER := 0;
 limite_transacciones NUMBER := 0;
 contador_transacciones NUMBER := 0;
 
 fecha DATE;
 
BEGIN  
      --dbms_output.put_line('Iniciando proceso de deteccion de fraude');  
      
      update clpm_incidencia_fraude set 
              st_incidencia_fraude = 'N', 
              co_usuario_modificador = 'system', 
              da_fecha_modificacion = sysdate 
      where to_char(da_fecha_proceso, 'dd/MM/yyyy') = to_char(sysdate-1, 'dd/MM/yyyy');          
      commit;   
      
      select  prt.va_limite_transacciones, prt.va_cantidad_limite_prod,
              prt.va_limite_transac_tarjeta, prt.va_cant_lim_prod_tarj
          into limite_transacciones_default, cantidad_lim_prod_default,
              limite_trans_tarj_default, cantidad_lim_prod_tarj_default
      from clpb_tipo_vehiculo pbt  
      left join clpr_producto_tipo_vehiculo prt on pbt.id = prt.co_tipo_vehiculo  
      where upper(pbt.de_tipo_vehiculo) = upper('DE_TIPO_VEHICULO_DEFAULT') 
      and pbt.st_tipo_vehiculo = 'Y'
      and pbt.st_es_default= 'Y';
      
     dbms_output.put_line('limite_transacciones_default '||limite_transacciones_default);  
     dbms_output.put_line('cantidad_lim_prod_default '||cantidad_lim_prod_default);  
     dbms_output.put_line('limite_trans_tarj_default '||limite_trans_tarj_default);  
     dbms_output.put_line('cantidad_lim_prod_tarj_default '||cantidad_lim_prod_tarj_default);  
   
    open cventas_producto;    
    loop
      fetch cventas_producto into cventa_producto_rec;
      exit when cventas_producto%NOTFOUND;
      
      dbms_output.put_line('Placa ' || cventa_producto_rec.placa);   
      
      cantidad_total_acum_cliente := cventa_producto_rec.sum_cantidad;
      contador_transacciones := cventa_producto_rec.contador;
      
      dbms_output.put_line('cantidad_total_acum_cliente '||cantidad_total_acum_cliente);  
      dbms_output.put_line('contador_transacciones '||contador_transacciones);  
      
      if(cventa_producto_rec.lim_producto is null or cventa_producto_rec.lim_transac is null) then 
        limite_transacciones := limite_transacciones_default;
        cantidad_limite_producto  :=  cantidad_lim_prod_default;       
      else
        limite_transacciones  :=  cventa_producto_rec.lim_transac;
        cantidad_limite_producto  :=  cventa_producto_rec.lim_producto;
      end if;  
    
      -- se inserta registro si es que vemos que hay fraude
      if (cantidad_total_acum_cliente >= cantidad_limite_producto and 
           contador_transacciones >= limite_transacciones ) then
           
          insert into clpm_incidencia_fraude (
                co_incidencia_fraude,                  
                de_placa, 
                co_producto, 
                va_cantidad_producto, 
                st_incidencia_fraude, 
                co_usuario_creador, 
                da_fecha_creacion, 
                va_cantidad_transaccion, 
                da_fecha_proceso
          ) values (
                SEQ_INCIDENCIA_FRAUDE.nextval, 
                cventa_producto_rec.placa, 
                cventa_producto_rec.producto, 
                cantidad_total_acum_cliente, 
                'Y', 
                'system', 
                sysdate, 
                contador_transacciones, 
                (sysdate-1));
      elsif (cantidad_total_acum_cliente >= cantidad_limite_producto) then
      
          insert into clpm_incidencia_fraude (
                co_incidencia_fraude, 
                de_placa, 
                co_producto, 
                va_cantidad_producto, 
                st_incidencia_fraude, 
                co_usuario_creador, 
                da_fecha_creacion, 
                da_fecha_proceso
          ) values (
                SEQ_INCIDENCIA_FRAUDE.nextval, 
                cventa_producto_rec.placa, 
                cventa_producto_rec.producto,  
                cantidad_total_acum_cliente, 
                'Y', 
                'system', 
                sysdate, 
                (sysdate-1));      
      elsif (contador_transacciones >= limite_transacciones ) then
      
          insert into clpm_incidencia_fraude (
                co_incidencia_fraude,                  
                de_placa, 
                co_producto, 
                st_incidencia_fraude, 
                co_usuario_creador, 
                da_fecha_creacion, 
                va_cantidad_transaccion, 
                da_fecha_proceso
          ) values (
                SEQ_INCIDENCIA_FRAUDE.nextval, 
                cventa_producto_rec.placa, 
                cventa_producto_rec.producto, 
                'Y', 
                'system', 
                sysdate, 
                contador_transacciones, 
                (sysdate-1));     
      end if;
      
    end loop;
    close cventas_producto;  
    
    limite_transacciones_tarjeta := limite_trans_tarj_default;
    cantidad_limite_producto  :=  cantidad_lim_prod_tarj_default;   
 
    open cventas_tarjeta;    
    loop
      fetch cventas_tarjeta into cventa_tarjeta_rec;
      exit when cventas_tarjeta%NOTFOUND;
    
      cantidad_total_acum_cliente := cventa_tarjeta_rec.sum_cantidad;
      contador_transacciones := cventa_tarjeta_rec.contador;     
      
     -- dbms_output.put_line('cantidad_total_acum_cliente '||cantidad_total_acum_cliente);  
     -- dbms_output.put_line('contador_transacciones '||contador_transacciones);  
      dbms_output.put_line('cventa_tarjeta_rec.code_tarjeta '||cventa_tarjeta_rec.code_tarjeta);  
	  		
      if (cantidad_total_acum_cliente >= cantidad_limite_producto and 
          contador_transacciones >= limite_transacciones_tarjeta) then
          
          insert into clpm_incidencia_fraude (
                co_incidencia_fraude, 
                co_cliente_persona, 
                co_producto, 
                va_cantidad_producto, 
                st_incidencia_fraude, 
                co_usuario_creador, 
                da_fecha_creacion, 
                co_tarjeta_persona, 
                va_cantidad_transaccion, 
                da_fecha_proceso
          ) values (
                SEQ_INCIDENCIA_FRAUDE.nextval, 
                cventa_tarjeta_rec.coClientePersona,
                cventa_tarjeta_rec.producto, 
                cantidad_total_acum_cliente, 
                'Y', 
                'system', 
                sysdate, 
                cventa_tarjeta_rec.code_tarjeta, 
                contador_transacciones, 
                (sysdate-1));
    
      elsif (cantidad_total_acum_cliente >= cantidad_limite_producto) then

          insert into clpm_incidencia_fraude (
                co_incidencia_fraude, 
                co_cliente_persona, 
                co_producto, 
                va_cantidad_producto, 
                st_incidencia_fraude, 
                co_usuario_creador, 
                da_fecha_creacion, 
                co_tarjeta_persona, 
                da_fecha_proceso
          ) values (
                SEQ_INCIDENCIA_FRAUDE.nextval, 
                cventa_tarjeta_rec.coClientePersona,
                cventa_tarjeta_rec.producto, 
                cantidad_total_acum_cliente, 
                'Y', 
                'system', 
                sysdate, 
                cventa_tarjeta_rec.code_tarjeta, 
                (sysdate-1));      
      elsif (contador_transacciones >= limite_transacciones_tarjeta) then 
      
          insert into clpm_incidencia_fraude (
                co_incidencia_fraude, 
                co_cliente_persona, 
                co_producto, 
                st_incidencia_fraude, 
                co_usuario_creador, 
                da_fecha_creacion, 
                co_tarjeta_persona, 
                va_cantidad_transaccion, 
                da_fecha_proceso
          ) values (
                SEQ_INCIDENCIA_FRAUDE.nextval, 
                cventa_tarjeta_rec.coClientePersona,
                cventa_tarjeta_rec.producto, 
                'Y', 
                'system', 
                sysdate, 
                cventa_tarjeta_rec.code_tarjeta, 
                contador_transacciones,
                (sysdate-1));       
                
      end if;
  end loop;
  close cventas_tarjeta;
  
  commit;   
  
  SP_NOTIFICAR_FRAUDE();
    
  exception
    when NO_DATA_FOUND then    
      dbms_output.put_line('HUBO UN ERROR DE DATA EN EL PROCEDURE');  
      err_num := SQLCODE;
      err_msg := SQLERRM;
      DBMS_OUTPUT.put_line('Error:'||TO_CHAR(err_num));
      DBMS_OUTPUT.put_line(err_msg);
      rollback;
    
    when INVALID_CURSOR then    
      dbms_output.put_line('EL CURSOR ES INVALIDO'); 
      err_num := SQLCODE;
      err_msg := SQLERRM;
      DBMS_OUTPUT.put_line('Error:'||TO_CHAR(err_num));
      DBMS_OUTPUT.put_line(err_msg);
      rollback;
     
    when OTHERS then
      dbms_output.put_line('HAY ALGUN ERROR EN EL STORE NO PREVISTO');   
      err_num := SQLCODE;
      err_msg := SQLERRM;
      DBMS_OUTPUT.put_line('Error:'||TO_CHAR(err_num));
      DBMS_OUTPUT.put_line(err_msg);
      rollback;

END SP_CONTROL_DE_FRAUDE;